package com.misterfat.excel.tool;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.IntSummaryStatistics;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Dict;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.template.Template;
import cn.hutool.extra.template.TemplateConfig;
import cn.hutool.extra.template.TemplateEngine;
import cn.hutool.extra.template.TemplateUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.cell.CellLocation;
import cn.hutool.poi.excel.cell.CellUtil;
import cn.hutool.poi.excel.style.StyleUtil;
import cn.hutool.setting.Setting;
import cn.hutool.setting.SettingUtil;

public class App {

	private static String baseDir = "D:\\excel-work";

	public static void main(String[] args) {

		// 加载数据
		Map<String, List<Map<String, Object>>> datas = getDatas();

		String templateDir = baseDir + File.separator + "template";
		String outDir = baseDir + File.separator + "xout";
		List<String> templateExcels = FileUtil.listFileNames(templateDir).stream()
				.filter(a -> a.endsWith(".xls") || a.endsWith(".xlsx")).collect(Collectors.toList());

		// 初始化模板引擎
		TemplateConfig config = new TemplateConfig();
		TemplateEngine engine = TemplateUtil.createEngine(config);
		Dict dict = Dict.create();
		for (Entry<String, List<Map<String, Object>>> e : datas.entrySet()) {
			dict.put(e.getKey(), e.getValue());
		}

		// 生成配置
		List<String> settings = FileUtil.listFileNames(templateDir).stream().filter(a -> a.endsWith(".ftl"))
				.collect(Collectors.toList());
		for (String setting : settings) {
			String templateFilePath = templateDir + File.separator + setting;
			String readUtf8String = FileUtil.readUtf8String(templateFilePath);
			Template template = engine.getTemplate(readUtf8String);
			String settingContent = template.render(dict);
			String path = templateDir + File.separator + FileUtil.getPrefix(setting) + ".conf";
			FileUtil.writeUtf8String(settingContent, path);
		}

		// 生成excel
		for (String fileName : templateExcels) {

			String templateFilePath = templateDir + File.separator + fileName;
			String outFilePath = outDir + File.separator + fileName;
			FileUtil.copy(new File(templateFilePath), new File(outFilePath), true);

			String propFilePath = templateDir + File.separator + FileUtil.getPrefix(fileName) + ".conf";
			Setting props = SettingUtil.get(propFilePath);
			List<String> sheets = props.getGroups();
			ExcelReader excelReader = ExcelUtil.getReader(new File(templateFilePath));
			ExcelWriter excelWriter = new ExcelWriter(new File(outFilePath));
//			excelWriter.disableDefaultStyle();

			for (int i = 0; i < sheets.size(); i++) {
				excelReader.setSheet(i);
				excelWriter.setSheet(i);
				String group = sheets.get(i);
				excelWriter.renameSheet(group);

				boolean hasDataGroup = true;
				int dataGroup = 0;
				while (hasDataGroup) {
					String groupDataKey = "data" + dataGroup;
					String dataSource = props.get(group, groupDataKey + ".source");
					String dataGroupBy = props.get(group, groupDataKey + ".groupby");
					String dataGroupAddrow = props.get(group, groupDataKey + ".groupaddrow");
					if (StrUtil.isNotBlank(dataSource)) {
						// 获得当前sheet数据
						List<Map<String, Object>> data = datas.get(dataSource);
						if (data != null && !data.isEmpty()) {
							Map<String, List<List<String>>> colKeyMappers = new HashMap<>();

							String cols = props.get(group, groupDataKey + ".cols");
							String rows = props.get(group, groupDataKey + ".rows");
							List<Map<String, String>> headers = getHeaders(props, group, groupDataKey, excelReader);

							String[] colArray = cols.split("-");
							int colstart = ExcelUtil.colNameToIndex(colArray[0]);
							String[] rowArray = rows.split("-");
							int rowstart = Convert.toInt(rowArray[0]) - 1;

							// 写数据
							List<Map<String, Object>> groupbylist = new ArrayList<>();
							List<Object> groupbyValues = data.stream().map(a -> a.get(dataGroupBy)).distinct()
									.collect(Collectors.toList());
							Map<Object, List<Map<String, Object>>> collect = data.stream()
									.collect(Collectors.groupingBy(a -> a.get(dataGroupBy)));
							for (int j = 0; j < groupbyValues.size(); j++) {

								Object groupbyValue = groupbyValues.get(j);
								List<Map<String, Object>> groupData = collect.get(groupbyValue);
								int groupDataSize = groupData.size();

								Map<String, List<String>> groupColKeyMapper = getGroupColKeyMapper(headers, rowstart,
										colstart, groupDataSize);
								Set<Entry<String, List<String>>> entrySet = groupColKeyMapper.entrySet();
								for (Entry<String, List<String>> e : entrySet) {
									String key = e.getKey();
									List<String> value = e.getValue();
									List<List<String>> list = colKeyMappers.get(key);
									if (list == null) {
										list = new ArrayList<>();
									}
									list.add(value);
									colKeyMappers.put(key, list);
								}

								for (int k = 0; k < groupDataSize; k++) {
									int rowIndex = rowstart + k;
									int colIndex = colstart;
									Map<String, String> groupKeyMapper = getGroupKeyMapper(headers, rowIndex, colstart);
									Map<String, Object> map = groupData.get(k);
									for (int l = 0; l < headers.size(); l++) {
										colIndex = colstart + l;
										Map<String, String> header = headers.get(l);
										String key = header.get("name").toString();
										String field = header.get("field").toString();
										Object value = null;
										if (isFormula(field)) {
											Cell cell = excelWriter.getOrCreateCell(colIndex, rowIndex);
											field = field.substring(1);
											for (Map<String, String> headMap : headers) {
												String head = headMap.get("name");
												String loc = groupKeyMapper.get(head);
												if (StrUtil.isNotBlank(loc)) {
													field = field.replace(head, loc);
												}
											}
											cell.setCellFormula(field);
										} else {
											value = map.get(field);
											excelWriter.writeCellValue(colIndex, rowIndex, value);
										}
										if (key.equals(dataGroupBy)) {
											Map<String, Object> m = new HashMap<>();
											m.put("row", rowIndex);
											m.put("col", colIndex);
											m.put("value", value);
											groupbylist.add(m);
										}
									}

								}
								rowstart += groupDataSize;
								// 分组添加行
								if (StrUtil.isNotBlank(dataGroupAddrow)) {
									int rowIndex = rowstart;
									Map<String, String> groupKeyMapper = getGroupKeyMapper(headers, rowIndex, colstart);
									String[] dataGroupAddCols = dataGroupAddrow.split(",");
									for (int f = 0; f < dataGroupAddCols.length; f++) {
										Map<String, String> map = headers.get(f);
										String headKey = map.get("name");
										int colIndex = f;
										String field = dataGroupAddCols[f];
										Object value = null;
										if (isFormula(field)) {
											Cell cell = excelWriter.getOrCreateCell(colIndex, rowIndex);
											field = field.substring(1);
											if (field.contains(":") || field.contains(",")) {
												for (Map<String, String> headMap : headers) {
													String head = headMap.get("name");
													String loc = groupKeyMapper.get(head);
													if (StrUtil.isNotBlank(loc)) {
														field = field.replace(head, loc);
													}
												}
											} else {
												for (Map<String, String> headMap : headers) {
													String head = headMap.get("name");
													List<String> list = groupColKeyMapper.get(head);
													if (list != null && !list.isEmpty()) {
														field = field.replace(head,
																list.get(0) + ":" + list.get(list.size() - 1));
													}
												}
											}
											cell.setCellFormula(field);
										} else {
											value = field;
											excelWriter.writeCellValue(colIndex, rowIndex, value);
										}
										if (headKey.equals(dataGroupBy)) {
											Map<String, Object> m = new HashMap<>();
											m.put("row", rowIndex);
											m.put("col", colIndex);
											m.put("value", groupbyValue);
											groupbylist.add(m);
										}
									}
									rowstart++;
								}
							}

							// 合并groupby单元格
							margeGroupSameCell(excelWriter, groupbylist);

							// 添加行
							String dataAddrow = props.get(group, groupDataKey + ".addrow");
							if (StrUtil.isNotBlank(dataAddrow)) {
								int rowIndex = rowstart;
								Map<String, String> groupKeyMapper = getGroupKeyMapper(headers, rowIndex, colstart);
								String[] split = dataAddrow.split(",");
								for (int f = 0; f < split.length; f++) {
									int colIndex = f;
									String field = split[f];
									Object value = null;
									if (isFormula(field)) {
										Cell cell = excelWriter.getOrCreateCell(colIndex, rowIndex);
										field = field.substring(1);
										if (field.contains(":") || field.contains(",")) {
											for (Map<String, String> headMap : headers) {
												String head = headMap.get("name");
												String loc = groupKeyMapper.get(head);
												if (StrUtil.isNotBlank(loc)) {
													field = field.replace(head, loc);
												}
											}
										} else {
											for (Map<String, String> headMap : headers) {
												String head = headMap.get("name");
												List<List<String>> list = colKeyMappers.get(head);
												if (list != null && !list.isEmpty()) {
													String loc = list.stream()
															.map(a -> a.get(0) + ":" + a.get(a.size() - 1))
															.collect(Collectors.joining(","));
													field = field.replace(head, loc);
												}
											}
										}
										cell.setCellFormula(field);
									} else {
										value = field;
										excelWriter.writeCellValue(colIndex, rowIndex, value);
									}
								}
								rowstart++;
							}

							// 设置单元格值
							String cellvalue = props.get(group, groupDataKey + ".cellvalue");
							if (StrUtil.isNotBlank(cellvalue)) {
								String[] split = cellvalue.split(",");
								for (String percell : split) {
									String[] cells = percell.split("<-");
									String cellName = cells[0];
									String field = cells[1];
									CellLocation location = ExcelUtil.toLocation(cellName);
									int colIndex = location.getX();
									int rowIndex = location.getY();
									if (isFormula(field)) {
										Cell cell = excelWriter.getOrCreateCell(colIndex, rowIndex);
										field = field.substring(1);
										for (Map<String, String> headMap : headers) {
											String head = headMap.get("name");
											List<List<String>> list = colKeyMappers.get(head);
											if (list != null && !list.isEmpty()) {
												String loc = list.stream()
														.map(a -> a.get(0) + ":" + a.get(a.size() - 1))
														.collect(Collectors.joining(","));
												field = field.replace(head, loc);
											}
										}
										cell.setCellFormula(field);
									} else {
										String value = field;
										excelWriter.writeCellValue(colIndex, rowIndex, value);
									}

								}
							}

						}

						dataGroup++;
					} else {
						hasDataGroup = false;
					}

				}

			}
			excelWriter.close();
		}
	}

	private static Map<String, String> getGroupKeyMapper(List<Map<String, String>> headers, int rowIndex,
			int colstart) {
		Map<String, String> groupKeyMapper = new HashMap<>();
		int colIndex = colstart;
		for (int l = 0; l < headers.size(); l++) {
			colIndex = colstart + l;
			Map<String, String> header = headers.get(l);
			String key = header.get("name").toString();
			String colName = ExcelUtil.indexToColName(colIndex) + (rowIndex + 1);
			groupKeyMapper.put(key, colName);
		}
		return groupKeyMapper;
	}

	private static Map<String, List<String>> getGroupColKeyMapper(List<Map<String, String>> headers, int rowstart,
			int colstart, int dataSize) {
		Map<String, List<String>> groupColKeyMapper = new HashMap<>();
		for (int k = 0; k < dataSize; k++) {
			int rowIndex = rowstart + k;
			int colIndex = colstart;
			Map<String, String> groupKeyMapper = new HashMap<>();
			for (int l = 0; l < headers.size(); l++) {
				colIndex = colstart + l;
				Map<String, String> header = headers.get(l);
				String key = header.get("name").toString();
				String colName = ExcelUtil.indexToColName(colIndex) + (rowIndex + 1);
				groupKeyMapper.put(key, colName);
				List<String> list = groupColKeyMapper.get(key);
				if (list == null) {
					list = new ArrayList<>();
				}
				list.add(colName);
				groupColKeyMapper.put(key, list);
			}
		}
		return groupColKeyMapper;
	}

	private static List<Map<String, String>> getHeaders(Setting props, String group, String groupDataKey,
			ExcelReader excelReader) {
		List<Map<String, String>> result = new ArrayList<>();
		String headerRows = props.get(group, groupDataKey + ".headerrow");
		String header = props.get(group, groupDataKey + ".header");
		String headerField = props.get(group, groupDataKey + ".headerfield");
		String[] headers = new String[0];
		String[] headerFields = new String[0];
		if (StrUtil.isNotBlank(header)) {
			headers = header.split(",");
		} else if (StrUtil.isNotBlank(headerRows)) {
			String[] split = headerRows.split(":");
			CellLocation start = ExcelUtil.toLocation(split[0]);
			CellLocation end = ExcelUtil.toLocation(split[1]);
			int startCol = start.getX();
			int startRow = start.getY();
			int endCol = end.getX();
			int endRow = end.getY();
			headers = new String[endCol - startCol + 1];
			for (int i = startCol; i <= endCol; i++) {
				List<String> h = new ArrayList<>();
				for (int j = startRow; j <= endRow; j++) {
					Object readCellValue = excelReader.readCellValue(i, j);
					if (readCellValue != null) {
						h.add(readCellValue.toString());
					}
				}
				headers[i] = h.stream().distinct().collect(Collectors.joining("."));
			}
		}
		if (StrUtil.isNotBlank(headerField)) {
			headerFields = headerField.split(",");
		} else {
			headerFields = headers;
		}

		for (int i = 0; i < headers.length; i++) {
			Map<String, String> h = new HashMap<>();
			String name = headers[i];
			String field = headerFields[i];
			h.put("name", name);
			h.put("field", field);
			result.add(h);
		}
		return result;
	}

	/**
	 * 
	 * 功能描述：是否是公式
	 *
	 * @param key
	 * @return
	 * 
	 * @author 耿沫然
	 *
	 * @since 2020年11月26日
	 *
	 * @update:[变更日期YYYY-MM-DD][更改人姓名][变更描述]
	 */
	private static boolean isFormula(String key) {
		return key.startsWith("=");
	}

	/**
	 * 
	 * 功能描述：合并内容相同的单元格
	 *
	 * @param sheet
	 * @param list
	 * 
	 * @author 耿沫然
	 *
	 * @since 2020年11月26日
	 *
	 * @update:[变更日期YYYY-MM-DD][更改人姓名][变更描述]
	 */
	private static void margeGroupSameCell(ExcelWriter excelWriter, List<Map<String, Object>> list) {
		Map<Object, List<Map<String, Object>>> collect = list.stream()
				.collect(Collectors.groupingBy(a -> a.get("value")));
		Set<Entry<Object, List<Map<String, Object>>>> entrySet = collect.entrySet();
		for (Entry<Object, List<Map<String, Object>>> e : entrySet) {
			List<Map<String, Object>> value = e.getValue();
			IntSummaryStatistics row = value.stream().mapToInt(a -> (int) a.get("row")).summaryStatistics();
			IntSummaryStatistics col = value.stream().mapToInt(a -> (int) a.get("col")).summaryStatistics();
			int minRownum = row.getMin();
			int maxRownum = row.getMax();
			int minColnum = col.getMin();
			int maxColnum = col.getMax();
			if (maxColnum != minColnum || maxRownum != minRownum) {
				CellStyle cellStyle = excelWriter.createCellStyle(minColnum, minRownum);
				cellStyle.setRotation((short) 255);
				StyleUtil.setAlign(cellStyle, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
				StyleUtil.setBorder(cellStyle, BorderStyle.THIN, IndexedColors.BLACK);
				CellUtil.mergingCells(excelWriter.getSheet(), minRownum, maxRownum, minColnum, maxColnum, cellStyle);
			}
		}
	}

	private static Map<String, List<Map<String, Object>>> getDatas() {
		Map<String, List<Map<String, Object>>> datas = new HashMap<>();
		String dataDir = baseDir + File.separator + "data";
		List<String> dataExcels = FileUtil.listFileNames(dataDir).stream()
				.filter(a -> a.endsWith(".xls") || a.endsWith("xlsx")).collect(Collectors.toList());
		for (String fileName : dataExcels) {
			String filePath = dataDir + File.separator + fileName;
			@SuppressWarnings("resource")
			List<Map<String, Object>> data = new ExcelReader(new File(filePath), 0).readAll();
			String name = FileUtil.getPrefix(filePath);
			datas.put(name, data);
		}
		String propFilePath = dataDir + File.separator + "data.conf";
		Setting props = SettingUtil.get(propFilePath);
		List<String> groups = props.getGroups();
		for (String group : groups) {
			String data1 = props.get(group, "data1");
			String data2 = props.get(group, "data2");

			List<Map<String, Object>> result = new ArrayList<>();
			if (data2 != null) {
				String join = props.get(group, "join");
				String on = props.get(group, "on");
				String[] split = on.split("=");
				String data1key = split[0];
				String data2key = split[1];
				List<Map<String, Object>> data1list = datas.get(data1);
				List<Map<String, Object>> data2list = datas.get(data2);
				switch (join) {
				case "left":
					Map<Object, List<Map<String, Object>>> data2map = data2list.stream()
							.collect(Collectors.groupingBy(a -> a.get(data2key)));
					result = data1list.stream().map(a -> {
						Object key = a.get(data1key);
						List<Map<String, Object>> list = data2map.get(key);
						if (list != null && !list.isEmpty()) {
							a.putAll(list.get(0));
						}
						return a;
					}).collect(Collectors.toList());
					break;
				case "right":
					Map<Object, List<Map<String, Object>>> data1map = data1list.stream()
							.collect(Collectors.groupingBy(a -> a.get(data1key)));
					result = data2list.stream().map(a -> {
						Object key = a.get(data1key);
						List<Map<String, Object>> list = data1map.get(key);
						if (list != null && !list.isEmpty()) {
							a.putAll(list.get(0));
						}
						return a;
					}).collect(Collectors.toList());
					break;

				default:
					Map<Object, List<Map<String, Object>>> map = data1list.stream()
							.collect(Collectors.groupingBy(a -> a.get(data1key)));
					Set<Object> keySet = map.keySet();
					result = data2list.stream().filter(a -> {
						return keySet.contains(a.get(data2key));
					}).map(a -> {
						Object key = a.get(data1key);
						List<Map<String, Object>> list = map.get(key);
						if (list != null && !list.isEmpty()) {
							a.putAll(list.get(0));
						}
						return a;
					}).collect(Collectors.toList());
					break;
				}
			} else {
				result = datas.get(data1);
			}
			String filter = props.get(group, "filter");
			if (StrUtil.isNotBlank(filter)) {
				String[] split = filter.split("=");
				result = result.stream().filter(a -> {
					String key = split[0];
					String value = split[1];
					return a.get(key).equals(value);
				}).collect(Collectors.toList());
			}
			String sort = props.get(group, "sort");
			if (StrUtil.isNotBlank(sort)) {
				String[] split = sort.split("=");
				String key = split[0];
				String type = split[1];
				result = result.stream().sorted((a, b) -> {
					@SuppressWarnings("unchecked")
					Comparable<Object> av = (Comparable<Object>) a.get(key);
					@SuppressWarnings("unchecked")
					Comparable<Object> bv = (Comparable<Object>) b.get(key);
					if ("ASC".equalsIgnoreCase(type)) {
						return av.compareTo(bv);
					}
					return bv.compareTo(av);
				}).collect(Collectors.toList());
			}
			datas.put(group, result);
		}
		return datas;
	}

}
